﻿Imports System.IO
Imports System.Reflection

''' <summary>
''' Represents a CSV-based data table.
''' </summary>
Public Class CsvTable
    Implements IEnumerable

    Private _fileName As String = String.Empty

    Private _schema As TableSchema = Nothing

    Private _columnNames As String()

    Private _data As New List(Of String())
    
    ''' <summary>
    ''' Initializes a new instance of the <see cref="CsvTable" /> class.
    ''' </summary>
    ''' <param name="fileName">Name of the file.</param>
    ''' <param name="schema">The associated schema of the table.</param>
    Private Sub New(ByVal fileName As String, ByVal schema As TableSchema)
        _fileName = fileName
        _schema = schema
    End Sub

    Friend Shared Function Load(ByVal fileName As String, ByVal schema As TableSchema) As CsvTable

        ' Create a new table, with all columns required
        Dim Result As New CsvTable(fileName, schema)
        Dim Fields As FieldSchema() = schema.GetStoreColumns
        Dim EntityColumns(Fields.Length - 1) As String
        For ColumnIndex As Integer = 0 To Fields.Length - 1
            EntityColumns(ColumnIndex) = Fields(ColumnIndex).Name
        Next
        Result._columnNames = EntityColumns.ToArray

        ' Read in all the lines
        Dim Lines As String() = File.ReadAllLines(fileName)
        If Lines.Length = 0 Then Throw New Exception("The CSV file must contain at least one row.")

        ' Process each of the lines in the file
        Dim FirstLine As Boolean = True
        Dim FileColumns As String() = Nothing
        For Each Line As String In Lines
            If Line <> "" Then

                ' Get the fields on the line
                Dim LineParts As String() = Line.Split(ControlChars.Tab)
                If Not FirstLine AndAlso LineParts.Length <> FileColumns.Length Then
                    Throw New Exception("Incorrect number of columns on at least one row of the CSV file.")
                End If

                ' Ensure control characters are restored
                For PartIndex As Integer = 0 To LineParts.Length - 1
                    LineParts(PartIndex) = LineParts(PartIndex).Replace("\", "\\").Replace(ControlChars.Tab, "\t").Replace(ControlChars.NewLine, "\n")
                Next

                If FirstLine Then

                    ' Set column names in the file
                    FirstLine = False
                    FileColumns = LineParts

                Else

                    ' Add this next row to the list
                    Dim Parts(EntityColumns.Length - 1) As String
                    For I As Integer = 0 To LineParts.Length - 1
                        Dim ColumnName As String = FileColumns(I)
                        Dim ActualColumnIndex As Integer = Array.IndexOf(Of String)(EntityColumns, ColumnName)
                        Parts(ActualColumnIndex) = LineParts(I)
                    Next
                    Result._data.Add(Parts)

                End If

            End If
        Next

        Return Result

    End Function

    Friend Shared Function Create(ByVal fileName As String, ByVal schema As TableSchema) As CsvTable

        ' Create the table
        Dim Result As New CsvTable(fileName, schema)

        ' Add columns to the table
        Dim ColumnNames As New List(Of String)
        For Each Field As FieldSchema In schema.GetStoreColumns
            ColumnNames.Add(Field.Name)
        Next
        Result._columnNames = ColumnNames.ToArray

        ' Save the table and return it
        Result.Save()
        Return Result

    End Function

    ''' <summary>
    ''' Gets the number of records contained in this database table.
    ''' </summary>
    ''' <value>The total number of records in the table.</value>
    Public ReadOnly Property RecordCount As Integer
        Get
            Return _data.Count
        End Get
    End Property

    Public Sub AppendRecord(ByVal rowData As String())

        If rowData.Length <> _columnNames.Length Then Throw New Exception("The number of columns in the given data does not match the required number of columns.")

        ' Get the next primary key value and set it
        Dim PrimaryKeyColumn As Integer = GetColumnIndex(_schema.PrimaryKey)
        rowData(PrimaryKeyColumn) = GetNextKeyValue()

        ' Add the row to the table
        _data.Add(rowData)
        Save()

    End Sub

    Public Sub AppendRecord(ByVal entityData As Dictionary(Of String, String))

        ' Iterate through each of the fields
        Dim Row(entityData.Count - 1) As String
        For Each Field As KeyValuePair(Of String, String) In entityData

            ' Get the index of the column
            Dim ColumnIndex As Integer = GetColumnIndex(Field.Key)

            ' Set the data
            Row(ColumnIndex) = Field.Value

        Next

        ' Get the next primary key value and set it
        Dim PrimaryKeyColumn As Integer = GetColumnIndex(_schema.PrimaryKey)
        Row(PrimaryKeyColumn) = GetNextKeyValue()

        ' Append the data row
        _data.Add(Row)
        Save()

    End Sub

    Public Sub AppendRecord(ByVal entity As BaseEntity)

        Dim RowData As String() = GetRowData(entity)

        ' Get the next primary key value and set it
        Dim PrimaryKeyColumn As Integer = GetColumnIndex(_schema.PrimaryKey)
        RowData(PrimaryKeyColumn) = GetNextKeyValue()

        _data.Add(RowData)
        Save()

    End Sub

    Public Function GetAllRecords() As BaseEntity()

        ' Process each of the rows in the table
        Dim Result As New List(Of BaseEntity)
        For RowIndex As Integer = 0 To RecordCount - 1

            ' Compose a strongly typed entity and add it to the list
            Dim NextData As Dictionary(Of String, String) = GetRecordAsDictionary(RowIndex)
            Dim NextEntity As BaseEntity = _schema.CreateEntityType
            NextEntity.PopulateFromDictionary(NextData)
            Result.Add(NextEntity)

        Next

        ' Return the list
        Return Result.ToArray

    End Function

    Public Function GetRecord(ByVal rowIndex As Integer) As BaseEntity

        Dim Data As Dictionary(Of String, String) = GetRecordAsDictionary(rowIndex)
        Dim Result As BaseEntity = _schema.CreateEntityType
        Result.PopulateFromDictionary(Data)
        Return Result

    End Function

    Public Function GetRecordAsRowData(ByVal rowIndex As Integer) As String()
        Return _data(rowIndex)
    End Function

    Public Function FindRecord(ByVal columnName As String, ByVal fieldValue As String) As BaseEntity

        ' Retrieve the requested the record from the given table
        Dim RecordIndex As Integer = GetRecordIndex(columnName, fieldValue)

        ' Return the result as a strongly typed entity
        If RecordIndex = -1 Then
            Return Nothing
        Else
            Dim Entity As Dictionary(Of String, String) = GetRecordAsDictionary(RecordIndex)
            Dim Result As BaseEntity = _schema.CreateEntityType
            Result.PopulateFromDictionary(Entity)
            Return Result
        End If

    End Function

    Public Function FindRecord(ByVal primaryKeyValue As String) As BaseEntity
        Return FindRecord(_schema.PrimaryKey, primaryKeyValue)
    End Function

    Public Function FindRecords(ByVal columnName As String, ByVal fieldValue As String) As BaseEntity()

        ' Process all the rows in the table
        Dim Result As New List(Of BaseEntity)
        Dim ColumnIndex As Integer = GetColumnIndex(columnName)
        For Each Row As String() In _data
            If Row(ColumnIndex) = fieldValue Then

                ' Found a record matching the search criteria
                Dim NextEntity As BaseEntity = _schema.CreateEntityType
                For I As Integer = 0 To _columnNames.Length - 1
                    Dim PropertyInfo As PropertyInfo = NextEntity.GetType.GetProperty(_columnNames(I))
                    PropertyInfo.SetValue(NextEntity, ConvertValue(Row(I), PropertyInfo.PropertyType), Nothing)
                Next
                Result.Add(NextEntity)

            End If
        Next
        Return Result.ToArray

    End Function

    Public Sub UpdateRecord(ByVal entity As BaseEntity)

        ' Which record is being edited?
        Dim RecordIdentifier As String = _schema.GetPrimaryKeyValue(entity)
        Dim RecordIndex As Integer = GetRecordIndex(_schema.PrimaryKey, RecordIdentifier)
        If RecordIndex = -1 Then Throw New Exception("Could not find the record.")

        ' Save changes to the record
        Dim RowData As String() = GetRowData(entity)
        _data(RecordIndex) = RowData
        Save()

    End Sub

    Public Sub UpdateRecord(ByVal rowIndex As Integer, ByVal rowData As String())

        If rowData.Length <> _columnNames.Length Then Throw New Exception("The number of columns in the given data does not match the required number of columns.")
        _data(rowIndex) = rowData
        Save()

    End Sub

    Public Sub DeleteRecord(ByVal entity As BaseEntity)

        ' Which record is being deleted?
        Dim RecordIdentifier As String = _schema.GetPrimaryKeyValue(entity)
        Dim RecordIndex As Integer = GetRecordIndex(_schema.PrimaryKey, RecordIdentifier)
        If RecordIndex = -1 Then Throw New Exception("Could not find the record.")

        ' Delete the record
        _data.RemoveAt(RecordIndex)
        Save()

    End Sub

    Public Sub DeleteRecord(ByVal rowIndex As Integer)

        _data.RemoveAt(rowIndex)
        Save()

    End Sub

    Public Sub DeleteRecords(ByVal columnName As String, ByVal fieldValue As String)

        ' Determine which rows need to be deleted
        Dim RowsToDelete As New Stack(Of Integer)
        Dim ColumnIndex As Integer = GetColumnIndex(columnName)
        For I As Integer = 0 To _data.Count - 1
            If _data(I)(ColumnIndex) = fieldValue Then RowsToDelete.Push(I)
        Next

        ' Delete the rows
        While RowsToDelete.Count > 0
            Dim RowIndex As Integer = RowsToDelete.Pop()
            _data.RemoveAt(RowIndex)
        End While
        Save()

    End Sub

    Private Function GetRecordIndex(ByVal columnName As String, ByVal fieldValue As String) As Integer

        Dim ColumnIndex As Integer = GetColumnIndex(columnName)
        For Index As Integer = 0 To _data.Count - 1
            If _data(Index)(ColumnIndex) = fieldValue Then Return Index
        Next
        Return -1

    End Function

    Private Function GetRowData(ByVal entity As BaseEntity) As String()

        ' TODO: Make this the other way around - fields in the entity not present in columns should not be ignored!

        ' Compose a row for inserting into the table
        Dim RowData(_columnNames.Length - 1) As String
        For ColumnIndex As Integer = 0 To _columnNames.Length - 1
            Dim ColumnName As String = _columnNames(ColumnIndex)
            RowData(ColumnIndex) = entity.GetType.GetProperty(ColumnName).GetValue(entity, Nothing)
        Next

        ' Return the result
        Return RowData

    End Function

    Private Function GetRecordAsDictionary(ByVal rowIndex As Integer) As Dictionary(Of String, String)

        Dim Result As New Dictionary(Of String, String)
        Dim Headers As String() = _columnNames
        Dim Row As String() = _data(rowIndex)
        For ColumnIndex As Integer = 0 To Headers.Length - 1
            Result.Add(Headers(ColumnIndex), Row(ColumnIndex))
        Next
        Return Result

    End Function

    Private Function GetColumnIndex(ByVal columnName As String) As Integer

        For I As Integer = 0 To _columnNames.Length - 1
            If _columnNames(I) = columnName Then Return I
        Next
        Throw New Exception("The column '" + columnName + "' does not exist.")

    End Function

    Private Sub Save()

        ' Create a file
        Using File As New StreamWriter(New FileStream(_fileName, FileMode.Create))

            ' Write columns
            WriteData(File, _columnNames)

            ' Write all rows in the data set
            For Each Row As String() In _data
                WriteData(File, Row)
            Next

        End Using

    End Sub

    Private Sub WriteData(ByVal writer As StreamWriter, ByVal data() As String)

        ' Write each field to the line
        Dim Line As String = String.Empty
        Dim First As Boolean = True
        For Each Field As String In data
            If Field Is Nothing Then Field = String.Empty
            If First Then First = False Else Line += ControlChars.Tab
            Line += Field.Replace("\", "\\").Replace(ControlChars.Tab, "\t").Replace(ControlChars.NewLine, "\n")
        Next

        ' Write the line to the file
        writer.WriteLine(Line)

    End Sub

    Private Function GetNextKeyValue() As String

        ' Check the type of the primary key
        Dim PrimaryKeyFieldType As Type = _schema.Fields(_schema.PrimaryKey).FieldType
        If TypeIsWholeNumberType(PrimaryKeyFieldType) Then

            ' Get the maximum value of the integer primary key column
            Dim Number As Integer = 1
            Dim ColumnIndex As Integer = GetColumnIndex(_schema.PrimaryKey)
            For Each Row As String() In _data
                Number = Math.Max(Number, Integer.Parse(Row(ColumnIndex)))
            Next

            ' Add 1 and return the result
            Return (Number + 1).ToString

        ElseIf PrimaryKeyFieldType Is GetType(Guid) Then

            ' Create a guid value
            Return Guid.NewGuid.ToString

        Else
            Throw New Exception("Cannot create a primary key value for field '" + _schema.PrimaryKey + "'.")
        End If

    End Function

    Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
        Return New CsvTableEnumerator(Me)
    End Function

End Class
